package org.dawfinn.droiditarian.data;

import java.util.Date;
import java.util.List;
import java.util.Set;
import java.util.TreeSet;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.util.Log;

public class DeedsDBAdapter {

	private static final String DB_NAME = "droiditarian.db";
	private static final String DEED_TABLE = "deeds";
	private static final int DB_VERSION = 5;
	private final DeedsDBOpenHelper openHelper;
	private static final String ID_KEY = "_id";
	private static final String NAME_KEY = "name";
	private static final String DESCRIPTION_KEY = "description";
	private static final String REASON_KEY = "reason";
	private static final String MORE_INFO_KEY = "moreInfo";
	private static final String APP_ENGINE_ID_KEY = "appEngineId";
	private static final String FAVORITE_KEY = "favorite";
	private static final String COMPLETED_KEY = "completed";
	private static final String RELEASE_DATE_KEY = "releaseDate";
	private static final String[] DEED_KEYS = new String[] { ID_KEY, NAME_KEY,
			DESCRIPTION_KEY, REASON_KEY, MORE_INFO_KEY, APP_ENGINE_ID_KEY,
			FAVORITE_KEY, COMPLETED_KEY, RELEASE_DATE_KEY };
	private static final String[] APP_ENGINE_ID_KEYS = new String[] { APP_ENGINE_ID_KEY };
	private static final String[] DATE_KEYS = new String[] { RELEASE_DATE_KEY };

	private static final short TRUE = 1;
	private static final short FALSE = 0;

	private SQLiteDatabase db;

	public DeedsDBAdapter(Context context) {
		this.openHelper = new DeedsDBOpenHelper(context, DB_NAME, null,
				DB_VERSION);
	}

	public void open() {
		db = openHelper.getWritableDatabase();
	}

	public void close() {
		if (db != null && db.isOpen())
			db.close();
	}

	public synchronized boolean insertDeeds(List<Deed> deeds) {
		// This was throwing an SQL exception when there weren't
		// any deeds, so I'm quick-returning for now.
		if (deeds.size() <= 0) {
			return false;
		}
		Cursor cursor = db.query(DEED_TABLE, APP_ENGINE_ID_KEYS,
				APP_ENGINE_ID_KEY + " in " + joinDeedIds(deeds), null, null, null,
				null);
		Set<String> ids = new TreeSet<String>();
		cursor.moveToFirst();
		while (!cursor.isAfterLast()) {
			ids.add(cursor.getString(0));
			cursor.moveToNext();
		}
		boolean deedsAdded = false;
		for (Deed deed : deeds) {
			if (!ids.contains(deed.getServerId())) {
				deedsAdded = true;
				db.insert(DEED_TABLE, null, deedToContentValues(deed));
			}
		}
		return deedsAdded;
	}

	public void updateDeed(Deed deed) {
		db.update(DEED_TABLE, deedToContentValues(deed), ID_KEY + " = ?",
				new String[] { "" + deed.getId() });
	}

	private String joinDeedIds(List<Deed> deeds) {
		String appEngineIds = "(";
		for (Deed d : deeds) {
			appEngineIds += "'" + d.getServerId() + "',";
		}
		appEngineIds = appEngineIds.substring(0, appEngineIds.length() - 1);
		appEngineIds += ")";
		Log.i("Droiditarian", "joined IDs: " + appEngineIds);
		return appEngineIds;
	}

	public Deed getDeed(int key) {
		Cursor cursor = db.query(DEED_TABLE, DEED_KEYS, ID_KEY + "=" + key, null,
				null, null, null);
		cursor.moveToFirst();
		return cursorToDeed(cursor);
	}

	public Cursor getDeedsCursor() {
		return db.query(DEED_TABLE, DEED_KEYS, null, null, null, null,
				RELEASE_DATE_KEY + " desc");
	}

	public Cursor getFavoritedDeedsCursor() {
		return db.query(DEED_TABLE, DEED_KEYS, FAVORITE_KEY + "=" + TRUE, null,
				null, null, RELEASE_DATE_KEY + " desc");
	}

	public Date getOldestDeedDate() {
		Cursor result = db.query(DEED_TABLE, DATE_KEYS, null, null, null, null,
				RELEASE_DATE_KEY + " asc", "1");
		result.moveToFirst();
		Date oldestDate = new Date();
		if (result.getCount() > 0) {
			oldestDate = new Date(result.getLong(result
					.getColumnIndex(RELEASE_DATE_KEY)));
		}
		return oldestDate;
	}

	public static Deed cursorToDeed(Cursor cursor) {
		return new Deed(cursor.getInt(cursor.getColumnIndex(ID_KEY)), cursor
				.getString(cursor.getColumnIndex(NAME_KEY)), cursor
				.getString(cursor.getColumnIndex(DESCRIPTION_KEY)), cursor
				.getString(cursor.getColumnIndex(REASON_KEY)), cursor
				.getString(cursor.getColumnIndex(MORE_INFO_KEY)), cursor
				.getString(cursor.getColumnIndex(APP_ENGINE_ID_KEY)), cursor
				.getShort(cursor.getColumnIndex(FAVORITE_KEY)) == TRUE, cursor
				.getShort(cursor.getColumnIndex(COMPLETED_KEY)) == TRUE, new Date(
				cursor.getLong(cursor.getColumnIndex(RELEASE_DATE_KEY))));
	}

	public static ContentValues deedToContentValues(Deed d) {
		ContentValues result = new ContentValues();
		if (d.hasId())
			result.put(ID_KEY, d.getId());
		result.put(NAME_KEY, d.getName());
		result.put(DESCRIPTION_KEY, d.getDescription());
		result.put(REASON_KEY, d.getExplanation());
		result.put(MORE_INFO_KEY, d.getMoreInfo());
		result.put(APP_ENGINE_ID_KEY, d.getServerId());
		result.put(FAVORITE_KEY, d.isFavorite() ? TRUE : FALSE);
		result.put(COMPLETED_KEY, d.isCompleted() ? TRUE : FALSE);
		result.put(RELEASE_DATE_KEY, d.getReleaseDate().getTime());
		return result;
	}

	private static class DeedsDBOpenHelper extends SQLiteOpenHelper {

		public DeedsDBOpenHelper(Context context, String name,
				CursorFactory factory, int version) {
			super(context, name, factory, version);
		}

		@Override
		public void onCreate(SQLiteDatabase db) {
			db.execSQL("create table " + DEED_TABLE + " (" + ID_KEY
					+ " integer primary key autoincrement, " + NAME_KEY
					+ " text not null, " + DESCRIPTION_KEY + " text not null, "
					+ REASON_KEY + " text not null, " + MORE_INFO_KEY
					+ " text not null, " + APP_ENGINE_ID_KEY + " text not null, "
					+ FAVORITE_KEY + " integer not null, " + COMPLETED_KEY
					+ " integer not null, " + RELEASE_DATE_KEY + " long not null);");
		}

		@Override
		public void onUpgrade(SQLiteDatabase db, int v1, int v2) {
			db.execSQL("drop table if exists " + DEED_TABLE);
			onCreate(db);
		}

	}
}
